<?php

/**
 * The default Database Adapter for MySQL Database.
 */
class DatabaseAdapter {
	/** 
	 * The database connection link identifier 
	 */
	protected $linkId;
	
	/**
	 * The last result
	 */
	protected $lastResult;
	
	/**
	 * Queries counter
	 */ 
	protected $queriesCounter;
	
	/**
	 * Simple cache of queries
	 */
	protected $cache;
	
	/**
	 * Creates and establishes a connection to the database, if the login details are correct. 
	 * If so, the specified database will be selected
	 *
	 * @param string username the database login
	 * @param string password the database password
	 * @param string hostname the host name (ex. localhost)
	 * @param string dbname the database name to be selected, once the connection is established
	 */
	public function __construct($username, $password, $hostname, $dbname) {
		$this->connect($username, $password, $hostname, $dbname);
		if ( !empty($dbname) ) {
			$this->selectDatabase($dbname);
		}
		$this->queriesCounter = 0;
		$this->cache = array();
	}
	
	/**
	 * Creates and establishes a connection to the database, if the login details are correct.
	 * Otherwise an exception will be thrown.
	 *
	 * @param string username the database login
	 * @param string password the database password
	 * @param string hostname the host name (ex. localhost)
	 */
	public function connect($username, $password, $hostname) {
		$this->linkId = @mysql_connect($hostname, $username, $password);
		if ( !is_resource($this->linkId) ) {
			throw new Exception('Database connection failed! ', mysql_errno());
		}
	}
	
	/**
	 * Attempts to select the specified database. In case of failure an exception will be thrown.
	 *
	 * @param string dbname the database name to be selected
	 */	
	public function selectDatabase($dbname) {
		$result = @mysql_select_db($dbname, $this->linkId);
		if ( !$result ) {
			throw new Exception('Unable to select database `'. $dbname .'` ', mysql_errno());
		}
	}
	
	/**
	 * Attempts to execute the specified SQL statement. If the execution is successfull,
	 * returns the number of rows selected or affected (depended on the type of SQL statement).
	 * Otherwise an exception will be thrown.
	 *
	 * @param string sql the SQL statement to be executed
	 */
	public function query($sql) {
		$sql = trim($sql);
		
		$hash = md5($sql);
		if ( array_key_exists($hash, $this->cache) ) {
			$this->lastResult = $this->cache[$hash];
			return;
		}
		
		$result = @mysql_query($sql, $this->linkId);
		if ( !$result ) {
			throw new Exception('Unable to execute query <code>`'. $sql .'`</code> ', mysql_errno());
		}
		
		// store the result for reference
		$this->cache[$hash] = $result;
		$this->lastResult = $result;
		unset($hash);
		
		// increase the number of queries executed so far
		$this->queriesCounter++;
		
		// in case of a select statement return the number of selected rows
		// otherwise return the number of affected rows
		if ( preg_match('/^select/i', $sql) ) {	
			return mysql_num_rows($this->lastResult);
		} else {
			return mysql_affected_rows($this->linkId);
		}
	}
	
	/**
	 * Selects the specifid columns from specified table
	 *
	 * @param string table the name of the table to be selected
	 * @param string columns the columns to be selected
	 */
	public function select($table, $columns=null) {
		Cinnamon::loadClass('DatabaseCollection'); 
		return new DatabaseCollection($this, $table, $columns);
	}
	
	/**
	 * Attempts to insert the data into the specified table
	 *
	 * @param string table the table name
	 * @param array|object data an associative array or object
	 * @return int the number of rows affected	 
	 */
	public function insert($table, $data) {
		$sql = 'INSERT INTO '. $table;	
		$sql .= ' ('. implode(', ', array_keys($data)) .')';
		$sql .= ' VALUES(\''. implode('\', \'', $data) .'\')';

		return ( $this->query($sql) );
	}

	/**
	 * Attempts to update the data in the specified table
	 *
	 * @param string table the table name
	 * @param array|object data an associative array or object
	 * @param string where the where clause of the update command
	 * @return int the number of rows affected	 
	 */
	public function update($table, $data, $where=null) {
		$set = array();
		foreach ( $data as $column=>$value ) {
			array_push($set, $column .'=\''. $value .'\'');
		}
	
		$sql = 'UPDATE '. $table;	
		$sql .= ' SET '. implode(', ', $set);
		if ( !empty($where) ) {
			$sql .= ' WHERE '. $where;
		}

		return ( $this->query($sql) );
	}
	
	/**
	 * Attempts to update the data from the specified table
	 *
	 * @param string table the table name
	 * @param string where the where clause of the delete command
	 * @return int the number of rows deletec	 
	 */
	public function delete($table, $where=null) {
		$sql = 'DELETE FROM '. $table;	
		if ( !empty($where) ) {
			$sql .= ' WHERE '. $where;
		}

		return ( $this->query($sql) );
	}	
	
	/**
	 * Returns an iterator for the result of the specified SQL statement. The SQL statement
	 * is an optional parameter and if not defined the previous SQL statement will be used
	 *
	 * @param string sql the SQL statement to be executed
	 * @return a record set iterator
	 */
	public function getIterator($sql=null) {
		if ( !empty($sql) ) {
			$this->query($sql);
		}
		
		Cinnamon::loadClass('DatabaseRecordSet');
		return new DatabaseRecordSet($this->lastResult);
	}		
	
	/**
	 * Returns an entire row from the result of the specified SQL statement. The SQL statement
	 * is an optional parameter and if is not defined the previous SQL statement will be used.
	 *
	 * @param string sql the SQL statement to be executed
	 * @param int row the row number to be returned, started from zero. The default value is zero (i.e first row).
	 * @return an entire row
	 */
	public function getRecord($sql=null, $row=0) {
		$itr = $this->getIterator($sql);
		for ( $i=0; $i<$row; $i++ ) {
			$itr->next();
		}
		return $itr->current();
	}
	
	/**
	 * Returns a single value from result of the specified SQL statement. The SQL statement
	 * is an optional parameter and if is not defined the previous SQL statement will be used.
	 *
	 * @param string sql the SQL statement to be executed
	 * @param int row the row number to be selected, started from zero. The default value is zero (i.e first row).
	 * @param int col the col number to be return, from the selected row, started from zero. The default value is zero (i.e first column)
	 * @return a single value
	 */
	public function getValue($sql=null, $row=0, $col=0) {
		if ( !empty($sql) ) {
			$this->query($sql);
		}
		return @mysql_result($this->lastResult, $row, $col);
	}
	
	/**
	 * Escapes the specified value in order to be stored in the database. This function does not apply only
	 * to string but as well as to arrays and objects.
	 *
	 * @param mixed value the value to be escaped. If the value is an object or array, it will be serialized.
	 * Otherwise some modifications will apply according to the specified type.
	 * @param mixed type the type of the value, in case which the value is neither object nor array. 
	 * The default type is 'string', while the supported types are 'int', 'float', 'string' and 'html'.
	 */
	public function escape($value, $type='string') {
		if ( is_object($value) || is_array($value) ) {
			return serialize($value);
		} else if ( strcmp($type, 'string') == 0 ) {
			$value = htmlspecialchars($value, ENT_QUOTES);
			$value = preg_replace('/&amp;(#[0-9]+|[a-z]+);/i', '&$1;', $value);
			return mysql_real_escape_string($value, $this->linkId);
		} else if ( strcmp($type, 'html') == 0 ) {
			return mysql_real_escape_string($value, $this->linkId);
		}
	}
	
	/**
	 * Unescapes the specified value.
	 *
	 * @param mixed value to be escaped
	 * @return the unescaped value
	 */
	public function unescape($value) {
		if ( false !== $gm = @ unserialize($value) ) {
			return $gm;
		}
		return $value;
	}
	
	/**
	 * Returns the last insert id (auto_increment key value)
	 * 
	 * @return the last insert id
	 */
	public function insertId() {
		return @mysql_insert_id($this->linkId);
	}
	
	/**
	 * Returns the date in database format  (ex 2005-05-27)
	 *
	 * @param int timestamp the time in seconds from Jan 01, 1970
	 * @param float offset offset from UTC time
	 */
	public function getDate($timestamp=null, $offset=0) {
		if ( $timestamp == null ) {
			$timestamp = time();
		}
		$timestamp = $timestamp + ( $offset * SECS_PER_HOUR );
		return gmdate('Y-m-d', $timestamp);
	}

	/**
	 * Returns the datetime in database format (ex 2005-05-27 23:55:45)
	 *
	 * @param int timestamp the time in seconds from Jan 01, 1970
	 * @param float offset offset from UTC time
	 */	
	public function getDatetime($timestamp=null, $offset=0) {
		if ( $timestamp == null ) {
			$timestamp = time();
		}
		$timestamp = $timestamp + ( $offset * SECS_PER_HOUR );
		return gmdate('Y-m-d H:i:s', $timestamp);
	}
	
	/**
	 * Adds a new column to the specified table
	 *
	 * @param string table the table name
	 * @param string column the column name
	 * @param string type the column type
	 */
	public function addTableColumn($table, $column, $type) {
		$sql = 'DESCRIBE '. $table .' '. $column;
		$record = $this->getRecord($sql);
		
		if ( !is_object($record) && !isset($record->Field) ) {
			$sql = 'ALTER TABLE '. $table .' ADD '. $column .' '. $type .' NOT NULL';
			$this->query($sql);
		}
	}
	
	//
	// Magic Functions (for table names and selects)
	//
	public function __get($name) {
		return CSEDB_PREFIX . $name;
	}
	public function __call($name, $args) {
		return $this->select($this->{$name}, $args[0]);
	}
	
	/**
	 * Returns the number of queries executed so far 
	 */
	public function queriesCount() {
		return $this->queriesCounter;
	}
	
	public function lastResource() {
		return $this->lastResult;
	}
	
	public function collection($table, $fields) {
		Cinnamon::loadClass('DatabaseCollection');
		return new DatabaseCollection($this, CSEDB_PREFIX . $table, $fields);
	}
}

?>